﻿CREATE PROCEDURE [dbo].[utl_DropRole]
	@RoleName sysname
AS
BEGIN

	SET NOCOUNT ON;

	/****** DROP ROLE MEMBERS ******/
	IF  EXISTS (SELECT * FROM dbo.sysusers WHERE name = @RoleName AND issqlrole = 1)
	Begin

		DECLARE @RoleMemberName sysname
		DECLARE Member_Cursor CURSOR FOR
		select [name]
		from dbo.sysusers 
		where uid in ( 
			select memberuid
			from dbo.sysmembers
			where groupuid in (
				select uid
				FROM dbo.sysusers where [name] = @RoleName AND issqlrole = 1))

		OPEN Member_Cursor;

		FETCH NEXT FROM Member_Cursor
		into @RoleMemberName

		WHILE @@FETCH_STATUS = 0
		BEGIN

			exec sp_droprolemember @rolename=@RoleName, @membername= @RoleMemberName

			FETCH NEXT FROM Member_Cursor
			into @RoleMemberName
		END;

		CLOSE Member_Cursor;
		DEALLOCATE Member_Cursor;
		
	end
	
	/****** DROP ROLE  ******/
	IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
	BEGIN
	DECLARE @SQLDrop NVARCHAR(max)
	SET @SQLDrop = 'DROP ROLE ['+@RoleName+']'
	EXECUTE sp_executesql @SQLDrop
	END
	
end